package ytl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormatSymbols;
import java.util.Calendar;


public class FinanceController {
    
    private String getMonthName(int month)
    {
        DateFormatSymbols symbols = new DateFormatSymbols();
        String[] monthNames = symbols.getMonths();
        return monthNames[month];
    }  
    
    public String GeneralFinancialReport(DBConnection connection, Calendar Start, Calendar End){
        
        String ReturnString = null;
        float initexpenses = 0x0, totalincome = 0x0;
        
        
        String StartDate, EndDate;
        StartDate = Integer.toString(Start.get(Calendar.YEAR)) + "/" + String.format("%02d", Start.get(Calendar.MONTH) + 1) + "/" + String.format("%02d", Start.get(Calendar.DATE));
        EndDate = Integer.toString(End.get(Calendar.YEAR)) + "/" + String.format("%02d", End.get(Calendar.MONTH) + 1) + "/" + String.format("%02d", End.get(Calendar.DATE) + 1);

        ResultSet rs;
        try {
            try {
                rs = connection.Query_Retrieve("SELECT SUM(product.PurchasePrice) AS FPurchasePrice FROM sales INNER JOIN product ON product.ProductID=sales.ProductID WHERE sales.Timestamp >= '" + StartDate + "' AND sales.Timestamp <= '" + EndDate + "'");
                while(rs.next())
                {
                    initexpenses = rs.getFloat("FPurchasePrice");
                }
            } catch (SQLException ex) {            }                         
        } catch (ClassNotFoundException ex) {        }
        
        try {
                rs = connection.Query_Retrieve("SELECT SUM(ActualPrice) AS TotalIncome FROM sales WHERE sales.Timestamp >= '" + StartDate + "' AND sales.Timestamp <= '" + EndDate + "'");
                while(rs.next())
                {
                    totalincome = rs.getFloat("TotalIncome");
                }
            } catch (ClassNotFoundException ex) {            }                     
        catch (SQLException ex) {
        }
            
        connection.DBClose();
        
        Start.get(Calendar.MONTH);
        ReturnString = "\n     General Financial Report\n"+
                       "     " + getMonthName(Start.get(Calendar.MONTH)) + " " + Start.get(Calendar.DATE) + ", " + (Start.get(Calendar.YEAR)) + " - " + getMonthName(End.get(Calendar.MONTH)) + " " + End.get(Calendar.DATE) + ", " + (End.get(Calendar.YEAR)) + "\n\n" +
                       "     Initial Expenses: Php " + String.format("%.2f", initexpenses) + "\n" +
                       "     Initial Tax Expenses: Php " + String.format("%.2f", initexpenses * 0.12) + "\n" +
                       "     Total Expenses: Php " + String.format("%.2f", initexpenses + (initexpenses*0.12)) + "\n\n" +
                       "     Total Income: Php " + String.format("%.2f", totalincome) + "\n" +
                       "     Total Net Income After Tax: Php " + String.format("%.2f", totalincome - initexpenses - (totalincome * 0.12));
        
        return ReturnString;
    }
    
    public String CompletedTransactionsReport(DBConnection connection, Calendar Chosen){
        
        String ReturnString = null, partString = "";
        
        ReturnString = "\n     Completed Transactions Report (single date only)\n"+
                       "     " + getMonthName(Chosen.get(Calendar.MONTH)) + " " + Chosen.get(Calendar.DATE) + ", " + (Chosen.get(Calendar.YEAR)) + "\n\n" +
                       "     Timestamp          Name          Charged Price          Quantity\n\n";
        
        ResultSet rs;
        try{
            String ChosenDate, ChosenDate2;
            ChosenDate = Integer.toString(Chosen.get(Calendar.YEAR)) + "/" + String.format("%02d", Chosen.get(Calendar.MONTH) + 1) + "/" + String.format("%02d", Chosen.get(Calendar.DATE));
            ChosenDate2 = Integer.toString(Chosen.get(Calendar.YEAR)) + "/" + String.format("%02d", Chosen.get(Calendar.MONTH) + 1) + "/" + String.format("%02d", Chosen.get(Calendar.DATE) + 1);
            
            try {
                rs = connection.Query_Retrieve("SELECT sales.Timestamp, product.Name, sales.ActualPrice, sales.Quantity FROM sales INNER JOIN product ON product.ProductID=sales.ProductID WHERE sales.Timestamp >= '" + ChosenDate + "' AND sales.Timestamp <= '" + ChosenDate2 + "'");
                while(rs.next())
                {
                    partString += "     " + String.format("%02d", rs.getTimestamp("Timestamp").getHours()) + ":" + String.format("%02d", rs.getTimestamp("Timestamp").getMinutes()) + ":" + String.format("%02d", rs.getTimestamp("Timestamp").getSeconds())
                                + "     " + String.format("%-20s", rs.getString("Name"))
                                + "     " + String.format("Php %-10.2f", rs.getFloat("ActualPrice"))
                                + "     " + String.format("%-5d", rs.getInt("Quantity")) + "\n";
                }  
            } catch (ClassNotFoundException ex) {
            }
            
            connection.DBClose();
        }
        catch(SQLException ex)
        {      
        }
        
        ReturnString += partString;               
        return ReturnString;
    }
    
    public String ProductStatisticsReport(DBConnection connection, Calendar Start, Calendar End){
        
        String ReturnString = null;
        String mostDemand = null, leastDemand = null, mostSales = null, leastSales = null;
        int mostDemandCount = 0x0, leastDemandCount = 0x0;
        float mostSalesCount = 0x0, leastSalesCount = 0x0;
        
        ResultSet rs;
        try{
            
            String StartDate, EndDate;
            StartDate = Integer.toString(Start.get(Calendar.YEAR)) + "/" + String.format("%02d", Start.get(Calendar.MONTH) + 1) + "/" + String.format("%02d", Start.get(Calendar.DATE));
            EndDate = Integer.toString(End.get(Calendar.YEAR)) + "/" + String.format("%02d", End.get(Calendar.MONTH) + 1) + "/" + String.format("%02d", End.get(Calendar.DATE) + 1);
            try {
                rs = connection.Query_Retrieve("SELECT product.Name, Q1.ProductID, Q1.Quantity FROM (SELECT sales.ProductID, SUM(sales.Quantity) AS Quantity FROM sales WHERE sales.Timestamp >= '" + StartDate + "' AND sales.Timestamp <= '" + EndDate + "' GROUP BY sales.ProductID ORDER BY sales.Quantity DESC LIMIT 1) AS Q1 INNER JOIN product ON product.ProductID = Q1.ProductID");
                while(rs.next())
                {
                    mostDemand = rs.getString("Name");
                    mostDemandCount = rs.getInt("Quantity");
                }
            } catch (ClassNotFoundException ex) {            }         
            
            try {
                rs = connection.Query_Retrieve("SELECT product.Name, Q1.ProductID, Q1.Quantity FROM (SELECT sales.ProductID, SUM(sales.Quantity) AS Quantity FROM sales WHERE sales.Timestamp >= '" + StartDate + "' AND sales.Timestamp <= '" + EndDate + "' GROUP BY sales.ProductID ORDER BY sales.Quantity LIMIT 1) AS Q1 INNER JOIN product ON product.ProductID = Q1.ProductID");
                while(rs.next())
                {
                    leastDemand = rs.getString("Name");
                    leastDemandCount = rs.getInt("Quantity");
                }
            } catch (ClassNotFoundException ex) {            }
            
            try {
                rs = connection.Query_Retrieve("SELECT product.Name, Q1.ProductID, Q1.Profit FROM (SELECT sales.ProductID, SUM(sales.Profit) AS Profit FROM sales WHERE sales.Timestamp >= '" + StartDate + "' AND sales.Timestamp <= '" + EndDate + "' GROUP BY sales.ProductID ORDER BY sales.Profit DESC LIMIT 1) AS Q1 INNER JOIN product ON product.ProductID = Q1.ProductID");
                while(rs.next())
                {
                    mostSales = rs.getString("Name");
                    mostSalesCount = rs.getFloat("Profit");
                }
            } catch (ClassNotFoundException ex) {              }
            
            try {
                rs = connection.Query_Retrieve("SELECT product.Name, Q1.ProductID, Q1.Profit FROM (SELECT sales.ProductID, SUM(sales.Profit) AS Profit FROM sales WHERE sales.Timestamp >= '" + StartDate + "' AND sales.Timestamp <= '" + EndDate + "' GROUP BY sales.ProductID ORDER BY sales.Profit LIMIT 1) AS Q1 INNER JOIN product ON product.ProductID = Q1.ProductID");
                while(rs.next())
                {
                    leastSales = rs.getString("Name");
                    leastSalesCount = rs.getFloat("Profit");
                }
            } catch (ClassNotFoundException ex) {            }
                        
            connection.DBClose();
        }
        catch(SQLException ex)
        {      
        }
        
        ReturnString = "\n     Product Statistics Report\n"+
                       "     " + getMonthName(Start.get(Calendar.MONTH)) + " " + Start.get(Calendar.DATE) + ", " + (Start.get(Calendar.YEAR)) + " - " + getMonthName(End.get(Calendar.MONTH)) + " " + End.get(Calendar.DATE) + ", " + (End.get(Calendar.YEAR)) + "\n\n" +
                       "     Most demanded product: " + mostDemand +", " + mostDemandCount + " units sold\n\n"+
                       "     Least demanded product: " + leastDemand +", " + leastDemandCount + " units sold\n\n"+
                       "     Most profitable product: " + mostSales +", " + String.format("Php %.2f", mostSalesCount) + " net income\n\n"+
                       "     Least profitable product: " + leastSales +", " + String.format("Php %.2f", leastSalesCount) + " net income\n\n";
        
        return ReturnString;
    }
    
    public String CalculatedFinancialsReport(DBConnection connection, Calendar Start, Calendar End){
        
        String ReturnString = null;
        float initexpenses = 0x0, totalincome = 0x0, lostToDiscount = 0x0;
        int days = 0;
        
        while(Start.before(End))
            {
                Start.add(Calendar.DAY_OF_MONTH, 1);
                days++;
            }
        Start.add(Calendar.DAY_OF_MONTH, -days);
        
        ResultSet rs;
        try{
            String StartDate, EndDate;
            StartDate = Integer.toString(Start.get(Calendar.YEAR)) + "/" + String.format("%02d", Start.get(Calendar.MONTH) + 1) + "/" + String.format("%02d", Start.get(Calendar.DATE));
            EndDate = Integer.toString(End.get(Calendar.YEAR)) + "/" + String.format("%02d", End.get(Calendar.MONTH) + 1) + "/" + String.format("%02d", End.get(Calendar.DATE) + 1);
            try {
                rs = connection.Query_Retrieve("SELECT product.PurchasePrice, sales.ActualPrice FROM sales INNER JOIN product ON product.ProductID=sales.ProductID WHERE sales.Timestamp >= '" + StartDate + "' AND sales.Timestamp <= '" + EndDate + "'");
                while(rs.next())
                {
                    initexpenses += rs.getFloat("PurchasePrice");
                    totalincome += rs.getFloat("ActualPrice");
                }
            } catch (ClassNotFoundException ex) {            }
            
            try {      
                rs = connection.Query_Retrieve("SELECT Profit FROM sales WHERE sales.Timestamp >= '" + StartDate + "' AND sales.Timestamp <= '" + EndDate + "' AND Profit < 0");
                while(rs.next())
                {                
                    lostToDiscount += Math.abs(rs.getFloat("Profit"));
                }
            } catch (ClassNotFoundException ex) {            }
            
            connection.DBClose();
        }
        catch(SQLException ex){
        }
        
        ReturnString = "\n     General Financial Report\n"+
                       "     " + getMonthName(Start.get(Calendar.MONTH)) + " " + Start.get(Calendar.DATE) + ", " + (Start.get(Calendar.YEAR)) + " - " + getMonthName(End.get(Calendar.MONTH)) + " " + End.get(Calendar.DATE) + ", " + (End.get(Calendar.YEAR)) + "\n\n" +
                       "     Average expenses per day: " + String.format("Php %.2f", initexpenses / days) + "\n" +
                       "     Average revenue per day: " + String.format("Php %.2f", totalincome / days) + "\n" +
                       "     Average profit per day: " + String.format("Php %.2f", (totalincome - initexpenses - (totalincome * 0.12)) / days) + "\n\n" +
                       "     Relative total revenue lost to tax: " + String.format("Php %.2f", totalincome * 0.12) + "\n" +
                       "     Relative total revenue lost to discount: " + 
                    String.format("Php %.2f", lostToDiscount) + "\n";
        
        return ReturnString;
    }
}
